package com.ailk.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.ailk.exception.DaoException;

@SuppressWarnings("unchecked")
public class DBUtil {

    private Connection conn;

    public DBUtil() throws DaoException {
        this.conn = ConnectionManager.getConnection();
    }

    public void begin() throws DaoException {
        if (conn != null) {
            try {
                conn.setAutoCommit(false);
            } catch (SQLException e) {
                throw new DaoException("can not begin transaction", e);
            }
        } else {
            throw new DaoException("connection not opened!");
        }
    }

    public void commit() throws DaoException {
        try {
            if (conn != null && !conn.getAutoCommit()) {
                conn.commit();
                conn.setAutoCommit(true);
            } else {
                if (conn == null) {
                    throw new DaoException("connection not opened!");
                } else {
                    throw new DaoException("first begin then commit please!");
                }
            }
        } catch (SQLException e) {
            throw new DaoException("can not commit transaction!", e);
        }
    }

    public void rollback() throws DaoException {
        try {
            if (conn != null && !conn.getAutoCommit()) {
                conn.rollback();
                conn.setAutoCommit(true);
            } else {
                if (conn == null) {
                    throw new DaoException("connection not opened!");
                } else {
                    throw new DaoException("first begin then rollback please!");
                }
            }
        } catch (SQLException e) {
            throw new DaoException("can not rollback transaction!", e);
        }
    }

    private List convert(ResultSet rs) throws DaoException {

        // record list
        List retList = new ArrayList();
        try {
            ResultSetMetaData meta = rs.getMetaData();
            // column count
            int colCount = meta.getColumnCount();
            // each record
            while (rs.next()) {
                Map recordMap = new HashMap();
                // each column
                for (int i = 1; i <= colCount; i++) {
                    // column name
                    String name = meta.getColumnName(i);
                    // column value
                    Object value = rs.getObject(i);
                    // add column to record
                    recordMap.put(name, value);
                }
                // ad record to list
                retList.add(recordMap);
            }
        } catch (SQLException ex) {
            throw new DaoException("can not convert result set to list of map", ex);
        }
        return retList;
    }

    private void apply(PreparedStatement pstmt, List params) throws DaoException {
        try {
            // if params exist
            if (params != null && params.size() > 0) {
                // parameters iterator
                Iterator it = params.iterator();
                // parameter index
                int index = 1;
                while (it.hasNext()) {
                    Object obj = it.next();
                    // if null set ""
                    if (obj == null) {
                        pstmt.setObject(index, "");
                    } else {
                        // else set object
                        pstmt.setObject(index, obj);
                    }
                    // next index
                    index++;
                }
            }
        } catch (SQLException ex) {
            throw new DaoException("can not apply parameter", ex);
        }
    }

    public List query(String sql, List params) throws DaoException {
        List result = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            this.apply(pstmt, params);
            rs = pstmt.executeQuery();
            result = this.convert(rs);
        } catch (SQLException ex) {
            throw new DaoException("can not execute query", ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    // nothing
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    // nothing
                }
            }
        }
        return result;
    }

    public Object queryOne(String sql, List params) throws DaoException {
        List list = this.query(sql, params);
        if (list == null || list.size() == 0) {
            throw new DaoException("data not exist");
        } else {
            Map record = (Map)list.get(0);
            if (record == null || record.size() == 0) {
                throw new DaoException("data not exist");
            } else {
                return record.values().toArray()[0];
            }
        }
    }

    public int execute(String sql, List params) throws DaoException {
        int ret = 0;
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            this.apply(pstmt, params);
            ret = pstmt.executeUpdate();
        } catch (SQLException ex) {
            throw new DaoException("", ex);
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return ret;
    }

    public List[] queryBatch(String[] sqlArray, List[] paramArray) throws DaoException {
        if (sqlArray.length != paramArray.length) {
            throw new DaoException("sql size not equal parameter size");
        } else {
            List[] rets = new ArrayList[sqlArray.length];
            for (int i = 0; i < sqlArray.length; i++) {
                rets[i] = new ArrayList();
                String sql = sqlArray[i];
                List param = paramArray[i];
                List ret = this.query(sql, param);
                rets[i].add(ret);
            }
            return rets;
        }
    }

    public int[] executeBatch(String[] sqlArray, List[] paramArray) throws DaoException {
        List rets = new ArrayList();
        if (sqlArray.length != paramArray.length) {
            throw new DaoException("sql size not equal parameter size");
        } else {
            for (int i = 0; i < sqlArray.length; i++) {
                int ret = this.execute(sqlArray[i], paramArray[i]);
                rets.add(new Integer(ret));
            }
            int[] retArray = new int[rets.size()];
            for (int i = 0; i < retArray.length; i++) {
                retArray[i] = ((Integer)rets.get(i)).intValue();
            }
            return retArray;
        }
    }

    public void close() throws DaoException {
        try {
            if (conn != null && conn.getAutoCommit()) {
                conn.close();
            } else {
                if (conn == null) {
                    throw new DaoException("can not close null connection, first new then close");
                } else {
                    throw new DaoException(
                        "transaction is running, rollbakc or commit befor close please.");
                }
            }
        } catch (SQLException ex) {
            throw new DaoException("Can not close common dao");
        }
    }

    public Connection getConnection() {
        return conn;
    }
}
